package com.xiguaji.tiktok.v1.tiktok.music.repository;

import com.xiguaji.tiktok.v1.tiktok.music.dto.MusicUsedDto;
import com.xiguaji.tiktok.v1.tiktok.music.model.Music;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface MusicRepository extends CrudRepository<Music,Long>,MusicExtRepository {

    @Query("select " +
            "m.title,m.cover_medium,m.author,count(*) mention," +
            "cast(avg(a.o_play_count) as decimal(10,2)) avg_view," +
            "sum(a.o_digg_count+a.o_share_count+a.o_comment_count)/sum(a.o_play_count)*100 engagement_rate," +
            "(avg( a.o_play_count ) - b.o_avg_views)/ b.o_avg_views*100 performance " +
            "from music m " +
            "left join aweme a on m.mid = a.o_music_id " +
            "left join blogger_main b ON a.o_uid = b.o_uid " +
            "where " +
            "a.o_uid = :uid and " +
            "a.o_create_time > :startTime and " +
            "m.title is not null " +
            "group by a.o_music_id order by mention desc limit 0,:size")
    List<MusicUsedDto> findByUidAndAwemeCreateDate(long uid,long startTime,int size);
}
